package com.ivncorp.evnproject.data;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ivncorp.evnproject.model.ObjectCot;
import com.ivncorp.evnproject.model.ObjectTram;
import com.ivncorp.evnproject.model.ObjectTuTT;
import com.ivncorp.evnproject.model.UploadTable;

public class DataBaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 2;

	// Database Name
	private static final String DATABASE_NAME = "evnprojectdatabase";

	// Contacts table name
	private static final String TABLE_UPLOADTABLE = "uploadtable";
	private static final String TABLE_TRAM = "Ttram";
	private static final String TABLE_TUTT = "TuTT";
	private static final String TABLE_COT = "Cot";

	// uploadtable Columns names
	private static final String KEY_ID = "ID";
	private static final String KEY_NAME = "Name";
	private static final String KEY_CODE = "Code";
	private static final String KEY_LATITUDE = "Lat";
	private static final String KEY_LONGITUDE = "Long";
	private static final String KEY_CAPACITY = "Capacity";
	private static final String KEY_TIME = "Time";
	private static final String KEY_UPLOADED = "Pploaded";

	private static final String KEY_1 = "image_1";
	private static final String KEY_2 = "image_2";
	private static final String KEY_3 = "image_3";
	private static final String KEY_4 = "image_4";
	private static final String KEY_5 = "image_5";

	String CREATE_UPLOADTABLE = "CREATE TABLE " + TABLE_UPLOADTABLE + " ("
			+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_CODE
			+ " TEXT," + KEY_1 + " TEXT," + KEY_2 + " TEXT," + KEY_3 + " TEXT,"
			+ KEY_4 + " TEXT," + KEY_5 + " TEXT," + KEY_LATITUDE + " TEXT,"
			+ KEY_LONGITUDE + " TEXT," + KEY_CAPACITY + " TEXT" + ")";

	String CREATE_TRAM = "CREATE TABLE " + TABLE_TRAM + " (" + KEY_ID
			+ " INTEGER PRIMARY KEY," + KEY_UPLOADED + " INTEGER," + KEY_1
			+ " TEXT," + KEY_2 + " TEXT," + KEY_3 + " TEXT," + KEY_4 + " TEXT,"
			+ KEY_5 + " TEXT," + KEY_CODE + " TEXT," + KEY_NAME + " TEXT,"
			+ KEY_CAPACITY + " TEXT," + KEY_TIME + " TEXT" + ")";

	String CREATE_COT = "CREATE TABLE " + TABLE_COT + " (" + KEY_ID
			+ " INTEGER PRIMARY KEY," + KEY_UPLOADED + " INTEGER," + KEY_1
			+ " TEXT," + KEY_2 + " TEXT," + KEY_3 + " TEXT," + KEY_CODE
			+ " TEXT," + KEY_NAME + " TEXT," + KEY_TIME + " TEXT" + ")";

	String CREATE_TUTT = "CREATE TABLE " + TABLE_TUTT + " (" + KEY_ID
			+ " INTEGER PRIMARY KEY," + KEY_UPLOADED + " INTEGER," + KEY_1
			+ " TEXT," + KEY_CODE + " TEXT," + KEY_NAME + " TEXT," + KEY_TIME
			+ " TEXT" + ")";

	public DataBaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {

		db.execSQL(CREATE_UPLOADTABLE);
		db.execSQL(CREATE_TRAM);
		db.execSQL(CREATE_COT);
		db.execSQL(CREATE_TUTT);

	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_UPLOADTABLE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRAM);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_COT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TUTT);
		// Create tables again
		onCreate(db);
	}

	public long addUploadTable(UploadTable uploadtable) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_ID, uploadtable.getId());
		values.put(KEY_NAME, uploadtable.getName());
		values.put(KEY_CODE, uploadtable.getCode());
		values.put(KEY_1, uploadtable.getImage1());
		values.put(KEY_2, uploadtable.getImage2());
		values.put(KEY_3, uploadtable.getImage3());
		values.put(KEY_4, uploadtable.getImage4());
		values.put(KEY_5, uploadtable.getImage5());
		values.put(KEY_LATITUDE, uploadtable.getLatitude());
		values.put(KEY_LONGITUDE, uploadtable.getLongitude());
		values.put(KEY_CAPACITY, uploadtable.getCapacity());
		// Inserting Row
		long a = db.insert(TABLE_UPLOADTABLE, null, values);
		db.close(); // Closing database connection
		return a;
		// TODO: handle exception
	}

	// Getting single contact
	public UploadTable getUploadTable(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_UPLOADTABLE, new String[] { KEY_ID,
				KEY_NAME, KEY_1, KEY_2, KEY_3, KEY_4, KEY_5, KEY_LATITUDE, KEY_LONGITUDE,
				KEY_CAPACITY }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		UploadTable uploadtable = new UploadTable();
		uploadtable.setId(Integer.parseInt(cursor.getString(0)));
		uploadtable.setName(cursor.getString(1));
		uploadtable.setCode(cursor.getString(2));
		uploadtable.setImage1(cursor.getString(3));
		uploadtable.setImage2(cursor.getString(4));
		uploadtable.setImage3(cursor.getString(5));
		uploadtable.setImage4(cursor.getString(6));
		uploadtable.setImage5(cursor.getString(7));
		uploadtable.setLatitude(cursor.getString(8));
		uploadtable.setLongitude(cursor.getString(9));
//		uploadtable.setCapacity(cursor.getString(10));

		return uploadtable;

	}

	// Getting All Contacts
	public ArrayList<UploadTable> getAllUploadTable() {
		ArrayList<UploadTable> uploadtableList = new ArrayList<UploadTable>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_UPLOADTABLE
				+ " ORDER BY name";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				UploadTable uploadtable = new UploadTable();
				uploadtable.setId(Integer.parseInt(cursor.getString(0)));
				uploadtable.setName(cursor.getString(1));
				uploadtable.setCode(cursor.getString(2));
				uploadtable.setImage1(cursor.getString(3));
				uploadtable.setImage2(cursor.getString(4));
				uploadtable.setImage3(cursor.getString(5));
				uploadtable.setImage4(cursor.getString(6));
				uploadtable.setImage5(cursor.getString(7));
				uploadtable.setLatitude(cursor.getString(8));
				uploadtable.setLongitude(cursor.getString(9));
//				uploadtable.setCapacity(cursor.getString(10));

				// Adding contact to list
				uploadtableList.add(uploadtable);
			} while (cursor.moveToNext());
		}
		// close inserting data from database
		db.close();
		// return contact list
		return uploadtableList;

	}

	// Updating single contact
	public int updateUploadTable(UploadTable uploadtable) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, uploadtable.getName());
		values.put(KEY_CODE, uploadtable.getCode());
		values.put(KEY_1, uploadtable.getImage1());
		values.put(KEY_2, uploadtable.getImage2());
		values.put(KEY_3, uploadtable.getImage3());
		values.put(KEY_4, uploadtable.getImage4());
		values.put(KEY_5, uploadtable.getImage5());
		values.put(KEY_LATITUDE, uploadtable.getLatitude());
		values.put(KEY_LONGITUDE, uploadtable.getLongitude());
		values.put(KEY_CAPACITY, uploadtable.getCapacity());
		// updating row
		return db.update(TABLE_UPLOADTABLE, values, KEY_ID + " = ?",
				new String[] { String.valueOf(uploadtable.getId()) });

	}

	// Deleting single contact
	public void deleteUploadTable(UploadTable uploadtable) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_UPLOADTABLE, KEY_ID + " = ?",
				new String[] { String.valueOf(uploadtable.getId()) });
		db.close();
	}

	public void deleteAllData() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_UPLOADTABLE, null, null);
		db.close();

	}

	

	/*------------------------Cot--------------------*/
	public long insertObjectCot(ObjectCot objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_UPLOADED, objecttable.getUploaded());
		values.put(KEY_1, objecttable.getImage_1());
		values.put(KEY_2, objecttable.getImage_2());
		values.put(KEY_3, objecttable.getImage_3());
		values.put(KEY_CODE, objecttable.getCode());
		values.put(KEY_NAME, objecttable.getName());
		values.put(KEY_TIME, objecttable.getTimestamp());
		// Inserting Row
		long a = db.insert(TABLE_COT, null, values);
		db.close(); // Closing database connection
		return a;
		// TODO: handle exception
	}

	// Getting All Cot
	public ArrayList<ObjectCot> getAllObjectCot() {
		ArrayList<ObjectCot> objecttableList = new ArrayList<ObjectCot>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_COT;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				ObjectCot objecttable = new ObjectCot();
				objecttable.setId_object(Integer.parseInt(cursor.getString(0)));
//				objecttable.setUploaded(Integer.parseInt(cursor.getString(1)));
				objecttable.setImage_1(cursor.getString(2));
				objecttable.setImage_2(cursor.getString(3));
				objecttable.setImage_3(cursor.getString(4));
				objecttable.setCode(cursor.getString(5));
				objecttable.setName(cursor.getString(6));
				objecttable.setTimestamp(cursor.getString(7));

				// Adding contact to list
				objecttableList.add(objecttable);
			} while (cursor.moveToNext());
		}
		// close inserting data from database
		db.close();
		// return contact list
		return objecttableList;

	}

	// Getting single Cot
	public ObjectCot getObjectCot(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_COT, new String[] { KEY_ID,
				KEY_UPLOADED, KEY_1, KEY_2, KEY_3, KEY_CODE, KEY_NAME,
				KEY_TIME }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		ObjectCot objecttable = new ObjectCot();
		objecttable.setId_object(Integer.parseInt(cursor.getString(0)));
//		objecttable.setUploaded(Integer.parseInt(cursor.getString(1)));
		objecttable.setImage_1(cursor.getString(2));
		objecttable.setImage_2(cursor.getString(3));
		objecttable.setImage_3(cursor.getString(4));
		objecttable.setCode(cursor.getString(5));
		objecttable.setName(cursor.getString(6));
		objecttable.setTimestamp(cursor.getString(7));
		return objecttable;

	}

	// Updating single Cot
	public int updateObjectCot(ObjectCot objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_UPLOADED, objecttable.getUploaded());
		values.put(KEY_1, objecttable.getImage_1());
		values.put(KEY_2, objecttable.getImage_2());
		values.put(KEY_3, objecttable.getImage_3());
		values.put(KEY_CODE, objecttable.getCode());
		values.put(KEY_NAME, objecttable.getName());
		values.put(KEY_TIME, objecttable.getTimestamp());
		// updating row
		return db.update(TABLE_COT, values, KEY_ID + " = ?",
				new String[] { String.valueOf(objecttable.getId_object()) });

	}

	// Deleting single Cot
	public Boolean deleteObjectCot(ObjectCot objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();
		int result = db.delete(TABLE_COT, KEY_ID + "=?",
				new String[] { String.valueOf(objecttable.getId_object()) });
		db.close();
		return result > 0;
	}

	/*------------------------TRAM--------------------*/
	public long insertObjectTram(ObjectTram objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_UPLOADED, objecttable.getUploaded());
		values.put(KEY_1, objecttable.getImage_1());
		values.put(KEY_2, objecttable.getImage_2());
		values.put(KEY_3, objecttable.getImage_3());
		values.put(KEY_4, objecttable.getImage_4());
		values.put(KEY_5, objecttable.getImage_5());
		values.put(KEY_CODE, objecttable.getCode());
		values.put(KEY_NAME, objecttable.getName());
		values.put(KEY_CAPACITY, String.valueOf(objecttable.getCapacity()));
		values.put(KEY_TIME, objecttable.getTimestamp());
		// Inserting Row
		long a = db.insert(TABLE_TRAM, null, values);
		db.close(); // Closing database connection
		return a;
		// TODO: handle exception
	}

	// Getting All tram
	public ArrayList<ObjectTram> getAllObjectTram() {
		ArrayList<ObjectTram> objecttableList = new ArrayList<ObjectTram>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_TRAM;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				ObjectTram objecttable = new ObjectTram();
				objecttable.setId_object(Integer.parseInt(cursor.getString(0)));
//				objecttable.setUploaded(Integer.parseInt(cursor.getString(1)));
				objecttable.setImage_1(cursor.getString(2));
				objecttable.setImage_2(cursor.getString(3));
				objecttable.setImage_3(cursor.getString(4));
				objecttable.setImage_4(cursor.getString(5));
				objecttable.setImage_5(cursor.getString(6));
				objecttable.setCode(cursor.getString(7));
				objecttable.setName(cursor.getString(8));
//				objecttable.setCapacity(cursor.getString(9));
				objecttable.setTimestamp(cursor.getString(10));

				// Adding contact to list
				objecttableList.add(objecttable);
			} while (cursor.moveToNext());
		}
		// close inserting data from database
		db.close();
		// return contact list
		return objecttableList;

	}

	// Getting single tram
	public ObjectTram getObjectTram(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_TRAM, new String[] { KEY_ID,
				KEY_UPLOADED, KEY_1, KEY_2, KEY_3, KEY_CODE, KEY_NAME,
				KEY_CAPACITY, KEY_TIME }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		ObjectTram objecttable = new ObjectTram();
		objecttable.setId_object(Integer.parseInt(cursor.getString(0)));
//		objecttable.setUploaded(Integer.parseInt(cursor.getString(1)));
		objecttable.setImage_1(cursor.getString(2));
		objecttable.setImage_2(cursor.getString(3));
		objecttable.setImage_3(cursor.getString(4));
		objecttable.setImage_4(cursor.getString(5));
		objecttable.setImage_5(cursor.getString(6));
		objecttable.setCode(cursor.getString(7));
		objecttable.setName(cursor.getString(8));
//		objecttable.setCapacity(cursor.getString(9));
		objecttable.setTimestamp(cursor.getString(10));

		return objecttable;

	}

	// Updating single tram
	public int updateObjectTram(ObjectTram objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_UPLOADED, objecttable.getUploaded());
		values.put(KEY_1, objecttable.getImage_1());
		values.put(KEY_2, objecttable.getImage_2());
		values.put(KEY_3, objecttable.getImage_3());
		values.put(KEY_4, objecttable.getImage_4());
		values.put(KEY_5, objecttable.getImage_5());
		values.put(KEY_CODE, objecttable.getCode());
		values.put(KEY_NAME, objecttable.getName());
		values.put(KEY_CAPACITY, String.valueOf(objecttable.getCapacity()));
		values.put(KEY_TIME, objecttable.getTimestamp());
		// updating row
		return db.update(TABLE_TRAM, values, KEY_ID + " = ?",
				new String[] { String.valueOf(objecttable.getId_object()) });

	}

	// Deleting single Tram
	public Boolean deleteObjectTram(ObjectTram objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();
		int result = db.delete(TABLE_TRAM, KEY_ID + "=?",
				new String[] { String.valueOf(objecttable.getId_object()) });
		db.close();
		return result > 0;
	}

	/*------------------------Tu TT--------------------*/
	public long insertObjectTuTT(ObjectTuTT objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_UPLOADED, objecttable.getUploaded());
		values.put(KEY_1, objecttable.getImage_1());
		values.put(KEY_CODE, objecttable.getCode());
		values.put(KEY_NAME, objecttable.getName());
		values.put(KEY_TIME, objecttable.getTimestamp());
		// Inserting Row
		long a = db.insert(TABLE_TUTT, null, values);
		db.close(); // Closing database connection
		return a;
		// TODO: handle exception
	}

	// Getting All TuTT
	public ArrayList<ObjectTuTT> getAllObjectTuTT() {
		ArrayList<ObjectTuTT> objecttableList = new ArrayList<ObjectTuTT>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_TUTT;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				ObjectTuTT objecttable = new ObjectTuTT();
				objecttable.setId_object(Integer.parseInt(cursor.getString(0)));
//				objecttable.setUploaded(Integer.parseInt(cursor.getString(1)));
				objecttable.setImage_1(cursor.getString(2));
				objecttable.setCode(cursor.getString(3));
				objecttable.setName(cursor.getString(4));
				objecttable.setTimestamp(cursor.getString(5));

				// Adding contact to list
				objecttableList.add(objecttable);
			} while (cursor.moveToNext());
		}
		// close inserting data from database
		db.close();
		// return contact list
		return objecttableList;

	}

	// Getting single TuTT
	public ObjectTuTT getObjectTuTT(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_TUTT, new String[] { KEY_ID,
				KEY_UPLOADED, KEY_1, KEY_2, KEY_3, KEY_CODE, KEY_NAME,
				KEY_CAPACITY, KEY_TIME }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		ObjectTuTT objecttable = new ObjectTuTT();
		objecttable.setId_object(Integer.parseInt(cursor.getString(0)));
//		objecttable.setUploaded(Integer.parseInt(cursor.getString(1)));
		objecttable.setImage_1(cursor.getString(2));
		objecttable.setCode(cursor.getString(3));
		objecttable.setName(cursor.getString(4));
		objecttable.setTimestamp(cursor.getString(5));

		return objecttable;

	}

	// Updating single TuTT
	public int updateObjectTuTT(ObjectTuTT objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_UPLOADED, objecttable.getUploaded());
		values.put(KEY_1, objecttable.getImage_1());
		values.put(KEY_CODE, objecttable.getCode());
		values.put(KEY_NAME, objecttable.getName());
		values.put(KEY_TIME, objecttable.getTimestamp());
		// updating row
		return db.update(TABLE_TUTT, values, KEY_ID + " = ?",
				new String[] { String.valueOf(objecttable.getId_object()) });

	}

	// Deleting single TuTT
	public Boolean deleteObjectTuTT(ObjectTuTT objecttable) {
		SQLiteDatabase db = this.getWritableDatabase();
		int result = db.delete(TABLE_TUTT, KEY_ID + "=?",
				new String[] { String.valueOf(objecttable.getId_object()) });
		db.close();
		return result > 0;
	}

}
